﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Threading;
using Citi.GLCosmos.Domain;
using Citi.Import.Util;
using Citi.RoutineBase;

namespace Citi.GLCosmos.SchedulerUI
{
    public class DatabaseUpdate : IExecutableRoutine
    {
        public IEnumerable<RoutineParameter> Parameters
        {
            get { return null; }
        }

        public static readonly Dictionary<string, string> ExcelColumnMappings = new Dictionary<string, string> { 
                { "[Column0]", "[Date]" },
                { "[Column1]", "Entity" },
                { "[Column2]", "Filial" },
                { "[Column3]", "Subsidiary" },
                { "[Column4]", "Money" },
                { "[Column5]", "Sector" },
                { "[Column6]", "Line" },
                { "[Column7]", "[Value]" },
                { "[Column8]", "TransactionType" },
                { "[Column9]", "TransactionCode" },
                { "[Column10]", "OPInput" },
                { "[Column11]", "OPAuth" },
                { "[Column12]", "Module" },
                { "[Column13]", "Prove" },
                { "[Column14]", "EventCode" },
                { "[Column15]", "EventNumber" },
                { "[Column16]", "ContabilityDate" },
                { "[Column17]", "HistoricOne" },
                { "[Column18]", "HistoricTwo" },

            };

        public object InitializeLifetimeService()
        {
            return null;
        }

        public void Rollback(ref ICurrentAction currentAction, Dictionary<string, object> parameters)
        {
        }

        public void Start(DateTime scheduledDate, ref ICurrentAction currentAction, Dictionary<string, object> parameters)
        {
            Thread.CurrentThread.CurrentUICulture = CultureInfo.CreateSpecificCulture("en-US");

            var directory = new DirectoryInfo(@"\\gruvnascti0009.wlb.lac.nsroot.net\Data_Grp1\ITFMTOOL\ITFMT_GLCOSMOS\PROD\");
            currentAction.Description = string.Format("Getting all files in {0}", directory.FullName);
            var fileName = directory.GetFiles().OrderByDescending(x => x.LastWriteTime).First();

            UpdateGLCosmos(string.Concat(directory, fileName), currentAction);
        }

        public void Stop(ref ICurrentAction currentAction, Dictionary<string, object> parameters)
        {

        }

        private static void UpdateGLCosmos(string fileName, ICurrentAction currentAction)
        {
            ExtractSubsidiary(fileName, currentAction);
            ExtractSectors(fileName, currentAction);
            ExtractAllData(fileName, currentAction);
            CopyFromTempTable(fileName, currentAction);
        }

        private static void CopyFromTempTable(string fileName, ICurrentAction currentAction)
        {
            currentAction.Description = "Copying data from Temp Table to SQL";
            GL.CopyFromTempTable(DateTime.ParseExact(System.IO.Path.GetFileName(fileName.ToString()).Substring(8, 8), "yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None));
            currentAction.Description = "Data copied";
        }

        private static void ExtractAllData(string fileName, ICurrentAction currentAction)
        {
            currentAction.Description = "Copying data from TXT to Temp Table";
            CopyTextFileToSQL("GLTemp", ConfigurationManager.ConnectionStrings["GLCosmos"].ConnectionString, ExcelColumnMappings, fileName);
            currentAction.Description = "Copy finished";
        }

        private static void ExtractSectors(string fileName, ICurrentAction currentAction)
        {
            currentAction.Description = "Getting Sectors from File";
            var sector = Conversion.ConvertFromTxt(Sector.Definitions, fileName, ';', false);
            currentAction.Description = "Saving Sectors";
            Sector.Save(sector);
            currentAction.Description = "Saved";
        }

        private static void ExtractSubsidiary(string fileName, ICurrentAction currentAction)
        {
            currentAction.Description = "Getting Subsidiaries from file";
            var subsidiaries = Conversion.ConvertFromTxt(Subsidiary.Definitions, fileName, ';', false);
            currentAction.Description = "Saving Subsidiaries";
            Subsidiary.Save(subsidiaries);
            currentAction.Description = "Saved";
        }

        public static void CopyTextFileToSQL(string destinationTable, string destinationConnectionString, Dictionary<string, string> columnMapping, string fileName)
        {
            using (var destinationConnection = new SqlConnection(destinationConnectionString))
            {
                destinationConnection.Open();

                using (var bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))
                {
                    bulkCopy.BatchSize = 5000;
                    bulkCopy.NotifyAfter = 10000;
                    bulkCopy.DestinationTableName = destinationTable;

                    if (columnMapping != null)
                        foreach (var column in columnMapping)
                            bulkCopy.ColumnMappings.Add(column.Key, column.Value);

                    bulkCopy.WriteToServer(GetDataSourceFromFile(fileName));
                }
            }
        }

        public static DataTable GetDataSourceFromFile(string fileName)
        {
            Thread.CurrentThread.CurrentUICulture = CultureInfo.CreateSpecificCulture("en-US");

            DataTable dt = new DataTable("GLCosmos");
            List<string> columns = null;

            var lines = File.ReadAllLines(fileName);

            if (lines.Count() > 0)
            {
                columns = lines[0].Split(new char[] { ';' }).ToList();

                for (int i = 0; i < columns.Count(); i++)
                    dt.Columns.Add(string.Concat("column", i));
            }

            for (int i = 0; i < lines.Count(); i++)
            {
                DataRow dr = dt.NewRow();
                string[] values = lines[i].Split(new char[] { ';' });

                for (int j = 0; j < values.Count() && j < columns.Count(); j++)
                {
                    if (j == 16)
                        dr[j] = dr[0];
                    else
                        if (j == 0)
                            dr[j] = DateTime.ParseExact(values[j], "yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None);
                        else
                            dr[j] = values[j];
                }

                dt.Rows.Add(dr);
            }
            return dt;
        }
    }
}
